package com.framework.base.util; 

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

import org.apache.log4j.Logger;

import com.framework.base.constant.WebConstants;
import com.framework.base.struts.BaseVo;

/**
 * Description:对查询进行通用组装
 * tabaleName(对应的表名):
 * Company: framework
 * @author：HYH
 * @version 1.0
 * @Date Jun 9, 2011 9:11:49 PM
 */
public class QueryUtil {
	private static Logger logger = Logger.getLogger(QueryUtil.class); // 作为日志的公用对象
	private static final String dataTypes="int,long,float,"; //以逗号结尾
	public static final String querySuffixs=",_start,_end"; //定义查询参数的后缀
	public static HashMap<String,Map<String,String>> propMap=new HashMap<String,Map<String,String>>();//用于管理类的属性
	/**
	 * 组装查询
	 * 规则:
	 * string默认使用模糊查询like
	 * 数字型(long,float,int等)默认使用=
	 * 数组型默认使用in
	 * 区间查询使用=<和>=
	 * @param paramsMap
	 * @return
	 */
	@SuppressWarnings({"rawtypes" })
	public static String generateQuerySql(Map<String,Object> paramsMap){
		StringBuffer querySql=new StringBuffer();//用于存储组装的sql
		String resultSql="";
		int andIndex=0; //计数器 决定是否添加and
		Class clzss=(Class) paramsMap.get(WebConstants.CLASS);
		addClzProp(clzss);
		Map<String,String> clzMap=propMap.get(clzss.getName());
		for (String paramName : paramsMap.keySet()) {
			String[] prop=paramName.split("_");
			//处理查询类型的操作符
			String queryType="";
			if(prop.length>=2){
				queryType=getOperateSymbol(prop[1]);
			}
			
			String fieldName=prop[0];
			String dataTypeName=clzMap.get(fieldName);
			
			String fieldNameStart=fieldName+"_start";
			String fieldNameEnd=fieldName+"_end";
			Object valueObj=paramsMap.get(paramName);
			
			//1不存在开始结束变量			if(clzMap.containsKey(fieldName)){
				//1.1对象为数组类型				if(valueObj!=null && valueObj.getClass().isArray()){
					Object[] valueArr=(Object[]) valueObj;
					String value=getStringFromArray(valueArr,isNumberProperty(dataTypeName));
					if(!StringUtil.isNullorEmpty(value)){
						querySql.append(andIndex>0?" and ":"");
						querySql.append(" "+fieldName+" in ("+value+") ");
						andIndex++;
					}
				}else{ //参数非数组类型					//1.2对象为字符串型						String value=StringUtil.getStringFromObj(valueObj);
						if(String.class.getName().equals(dataTypeName)&&(!paramsMap.containsKey(fieldNameStart)
								||!paramsMap.containsKey(fieldNameEnd))){
							if(!StringUtil.isNullorEmpty(value)){
								queryType=(queryType==null||"".equals(queryType))?"like":queryType;
								querySql.append(andIndex>0?" and ":"");
								querySql.append(" "+fieldName+" "+queryType+" '%"+value+"%' ");
								andIndex++;
							}
						}
					//1.3对象为数字类型				if(prop.length==1){
					if(isNumberProperty(dataTypeName)){
						if(!StringUtil.isNullorEmpty(value)){
							queryType=(queryType==null||"".equals(queryType))?"=":queryType;
							querySql.append(andIndex>0?" and ":"");
							querySql.append(" "+fieldName+" "+queryType+" "+value+" ");
							andIndex++;
						}
					}
				}
				}
			}
			//2.变量查询存在开始值或结束值			if(paramsMap.containsKey(fieldNameStart)||paramsMap.containsKey(fieldNameEnd)){
				Object valueStartObj=paramsMap.get(fieldNameStart);
				Object valueEndObj=paramsMap.get(fieldNameEnd);
				String valueStart=StringUtil.getStringFromObj(valueStartObj);
				String valueEnd=StringUtil.getStringFromObj(valueEndObj);
				String useValueStart=valueStart;
				String useValueEnd=valueEnd;
				//2.1非数字类型加上引号				if(!isNumberProperty(dataTypeName)){
					useValueStart="'"+useValueStart+"'";
					useValueEnd="'"+useValueEnd+"'";
				}
				//2.2开始变量存在结束变量不存在的情况				if(!StringUtil.isNullorEmpty(valueStart)&&StringUtil.isNullorEmpty(valueEnd)){
					querySql.append(andIndex>0?" and ":"");
					querySql.append(" "+fieldName+">="+useValueStart+" ");
					andIndex++;
				}
				//2.3结束变量存在开结变量不存在的情况				if(!StringUtil.isNullorEmpty(valueEnd)&&StringUtil.isNullorEmpty(valueStart)){
					querySql.append(andIndex>0?" and ":"");
					querySql.append(" "+fieldName+"<='"+valueEnd+"' ");
					andIndex++;
				}
				
				//2.4开始变量结束变量都存在的情况				if(!StringUtil.isNullorEmpty(valueEnd)&&!StringUtil.isNullorEmpty(valueStart)){
					querySql.append(andIndex>0?" and ":"");
					querySql.append(" "+fieldName+" between "+useValueStart+" and "+useValueEnd+"");
					andIndex++;
				}
			}
		}
		logger.info("查询条件:"+querySql.toString());
		
		return querySql.toString();
	}
	
	
	/**
	 * 根据代理字符串获取操作符
	 * @param delegateStr 操作符的代理字符串
	 * @return
	 */
	public static String getOperateSymbol(String delegateStr){
		String operateSymbol="like";
		if("start".equals(delegateStr)||"end".equals(delegateStr)){
			operateSymbol="";
		}
		if("eq".equals(delegateStr)){
			operateSymbol="=";
		}
		return operateSymbol;
	}
	
	
	/**
	 * 判断参数名是否在vo属性中存在
	 * @param vo
	 */
	public static boolean isParamInVo(BaseVo vo,String key){
		boolean bool=false;//默认参数不存在
		//判断类的反射信息是否存在
		if(QueryUtil.propMap.get(vo.getClass().getName())==null){
			QueryUtil.addClzProp(vo.getClass());
		}
		//对数据进行转发
		Map<String,String> clzMap=QueryUtil.propMap.get(vo.getClass().getName());
		for (String fieldName : clzMap.keySet()) {
			String[] paramNames=QueryUtil.querySuffixs.split(",");
			for (String suffix : paramNames) {
				String paramName=fieldName+suffix;
					if(key.equals(paramName)){
						bool=true;
						break;
					}
				}
			if(bool)break;
			}
		return bool;
		}
	
	
	/**
	 * 将数据转为以逗号隔开的字符串
	 * @param valueArr
	 * @return
	 */
	public static String getStringFromArray(Object[] valueArr,boolean isNumber){
		StringBuffer resultSb=new StringBuffer();
		for (Object value : valueArr) {
			if(!StringUtil.isNullorEmpty(value.toString())){
				if(!isNumber){//不为数字类型，加单引号
					resultSb.append("'").append(value).append("'");
				}else{//数字类型
					resultSb.append(value);
				}
				resultSb.append(",");
			}
		}
		if(resultSb.length()==0){
			return "";
		}else{
			return resultSb.toString().substring(0,resultSb.length()-1);
		}
	}
	
	
	/**
	 * 判断对象是否是数字类型
	 * @param valueObj
	 * @param dataTypeName
	 * @return
	 */
	public static boolean isNumberProperty(String dataTypeName){
		if((Long.class.getName().equals(dataTypeName))||(Integer.class.getName().equals(dataTypeName))
				||(Float.class.getName().equals(dataTypeName))
				||(dataTypes.startsWith(dataTypeName+",")
				||dataTypes.indexOf(","+dataTypeName+",")>=0)){
			return true;
		}else{
			return false;
		}
	}
	
	
	/**
	 * 将类的属性的信息用map保存起来以免每次都要进行反射
	 * @param clzss
	 */
	public static void addClzProp(@SuppressWarnings("rawtypes") Class clzss){
		if(propMap.get(clzss.getName())==null){
			Map<String,String> clzMap=new HashMap<String, String>();
			Field[] fields = clzss.getDeclaredFields();
			for (Field field : fields) {
				field.setAccessible(true);
				String fieldName=field.getName();
				String fieldTypeName=field.getType().getName();
				clzMap.put(fieldName, fieldTypeName);
			}
			clzMap.put("createidf", "java.lang.String");
			clzMap.put("deptidf","java.lang.String");
			propMap.put(clzss.getName(),clzMap);
		}
	}
	
	
	/**
	 * 判断指定字段的值是否重复动态生成sql语句
	 * @author lbx
	 * @createTime 2011-07-02
	 * @param voCls
	 * @param keyColumn
	 * @param paramMap
	 * @param selectType
	 * @return
	 */
	@SuppressWarnings({"rawtypes" })
	public static String checkDuplicateSQL(Class voCls,String keyColumn, Map<String, String> paramMap,String selectType){
		Set<String> set = paramMap.keySet();
		Iterator<String> iterator = set.iterator();
		StringBuffer buffer=new StringBuffer();
		int andIndex=0; //计数器 决定是否添加and
		String resultSql="";
		while(iterator.hasNext()){
			String column = iterator.next();
			String value = paramMap.get(column);
			if(StringUtil.nullToEmpty(value).length()>0){
				String ColumnName = column;
				if (column.indexOf(".") != -1){
					ColumnName = column.substring(column.indexOf(".") + 1);
				}
				String methodName = "get" + ColumnName.substring(0, 1).toUpperCase() + ColumnName.substring(1);
				try {
					Method method = voCls.getDeclaredMethod(methodName, null);
					if(QueryUtil.isNumberProperty(value)&&QueryUtil.isNumberProperty(method.getReturnType().getName())){
						if(("," + keyColumn + ",").indexOf("," + column + ",") != -1){ //判断字段是否含有包含关系
							buffer.append(andIndex>0?" and ":"");
							buffer.append(ColumnName+"<>"+StringUtil.nullToEmpty(value));
							andIndex++;
						}else{
							buffer.append(andIndex>0?" and ":"");
							buffer.append(ColumnName+"="+StringUtil.nullToEmpty(value));
							andIndex++;
						}
					}else{
						if(("," + keyColumn + ",").indexOf("," + column + ",") != -1){
							buffer.append(andIndex>0?" and ":"");
							buffer.append(ColumnName+"<>'"+StringUtil.nullToEmpty(value)+"'");
							andIndex++;
						}else{
							if(selectType.equals("=")){
								buffer.append(andIndex>0?" and ":"");
								buffer.append(ColumnName+"='"+StringUtil.nullToEmpty(value)+"'");
								andIndex++;
							}else{
								buffer.append(andIndex>0?" and ":"");
								buffer.append(ColumnName+" like '%"+StringUtil.nullToEmpty(value)+"%'");
								andIndex++;
							}
						}
					}
				} catch (Exception e) {
					e.printStackTrace();
				} 
			}
		}
		if(buffer.length()>0){
			resultSql=" where "+buffer.toString();
			return resultSql;
		}else{
			return "";
		}
	}
}
